import time
import pandas as pd
import numpy as np
from datetime import datetime
from sqlalchemy import and_
from app.utils.common_func_defs import *
from app.models.data_base_models import *
from app.services import get_base_session as get_session, get_engine


####################################################### 私有函数定义 ########################################################
'''—————————————————————————————方法：表全量更新—————————————————————————————'''
def allupdate_insert(df: pd.DataFrame, class_name):
    info='success'
    # try:
    #连接数据库
    session = get_session()

    # 转换数据为字典
    df = df.to_dict(orient='records')

    # 删除原有数据
    session.query(class_name).delete()

    # 批量插入
    objects = [class_name(**eachline) for eachline in df]
    session.add_all(objects)
    print('开始commit')
    start_time = time.time()  # 记录程序开始运行时间
    session.commit()
    end_time = time.time()  # 记录程序结束运行时间
    print('此次commit ：cost %f second' % (end_time - start_time))

    # 关闭会话
    print('commit结束')
    session.close()
    # except:
    #     info='整表刷新失败'
    # return info


####################################################### 加工方法定义 ########################################################
'''—————————————————————————————方法：根据上传内容全量更新—————————————————————————————'''
def upload_allupdate_insert(upload_df: pd.DataFrame,table_zh_name, class_name):
    # 上传结果info
    info='success'
    # try:
    # 得到处理后的上传表（中文列名转英文列名）
    df=upload_field_corr(upload_df, table_zh_name)

    #连接数据库
    session = get_session()

    # 添加上传该条数据记录的时间和插入该条数据记录的时间列
    df["upload_time"] = (datetime.now()).strftime("%Y-%m-%d %H:%M:%S")
    df["create_time"] = (datetime.now()).strftime("%Y-%m-%d %H:%M:%S")
    # 转换数据为字典
    df = df.to_dict(orient='records')

    # 删除原有数据
    session.query(class_name).delete()

    # 批量插入
    objects = [class_name(**eachline) for eachline in df]
    session.add_all(objects)
    print('开始commit')
    start_time = time.time()  # 记录程序开始运行时间
    session.commit()
    end_time = time.time()  # 记录程序结束运行时间
    print('此次commit ：cost %f second' % (end_time - start_time))

    # 关闭会话
    print('commit结束')
    session.close()
    # except:
    #     info='上传失败：请检查数据表中字段是否符合模板要求'
    # return info


'''—————————————————————————————方法：根据上传内容按stattime刷新数据—————————————————————————————'''
def upload_nostattime_insert(upload_df: pd.DataFrame,table_zh_name, class_name):
    # 得到处理后的上传表（中文列名转英文列名）
    df=upload_field_corr(upload_df, table_zh_name)

    # 将处理后的上传表存入数据库（按stat_time更新数据）
    # 上传结果info
    info='success'

    # 上传的数据一定有得stat_time列，格式一定为date
    if 'stat_time' not in df.columns:
        info='上传失败：请检查数据表中字段是否符合模板要求（缺少数据统计时间列）'
        return info
    # try:
    df['stat_time'] = pd.to_datetime(df['stat_time'])
    # except:
    #     info='上传失败：请检查数据表中字段是否符合模板要求（数据统计时间列不符合日期格式要求）'
    #     return info

    # try:
    #连接数据库
    session = get_session()

    # 检测想插入的数据的stat_time是否在数据库里面有东西，有就删了,由此达到刷新数据的效果
    df['stat_time'] = df['stat_time'].astype(str)
    stat_time_ls = list(df["stat_time"].drop_duplicates())
    session.query(class_name).filter(class_name.stat_time.in_(stat_time_ls)).delete()
    # 将数据按stat_time排序从早到晚排序
    df = df.sort_values(by='stat_time')
    # 添加上传该条数据记录的时间和插入该条数据记录的时间列
    df["upload_time"] = (datetime.now()).strftime("%Y-%m-%d %H:%M:%S")
    df["create_time"] = (datetime.now()).strftime("%Y-%m-%d %H:%M:%S")
    # 转换数据为字典
    df = df.to_dict(orient='records')
    # 批量插入
    objects = [class_name(**eachline) for eachline in df]
    session.add_all(objects)
    session.commit()
    # 关闭会话
    session.close()
    # except:
    #     info='上传失败：请检查数据表中字段是否符合模板要求'
    # return info


'''—————————————————————————————方法：根据上传内容生成商品指标表good_indicator—————————————————————————————'''
def generate_69info():
    """
    读入csv生成
    :param file_name:
    :return:
    """
    engine = get_engine()
    df_tp = pd.read_sql_query('SELECT * FROM good_product_combination', engine)
    df_tp.drop(['id', 'upload_time', 'create_time'], axis=1, inplace=True)
    #['product_combination', 'sixty_nine_code', 'product_code','product_bar_code', 'product_num']

    ###处理开单价###
    # 连接表
    shangpin_jiage = pd.read_sql_query('SELECT * FROM good_product_price', engine)
    shangpin_jiage.drop(['id', 'upload_time', 'create_time', 'product_code', 'product_name'], axis=1, inplace=True)

    df_type = pd.read_sql_query('SELECT * FROM good_product_code', engine)
    df_type.drop(['id', 'upload_time', 'create_time', 'product_code'], axis=1, inplace=True)

    shangpin_jiage = pd.merge(df_type, shangpin_jiage, on='product_bar_code', how='left')
    shangpin_jiage.fillna(0, inplace=True)  # 把type表中存在，但是价格体系表中不存在的值定为0
    shangpin_jiage['product_series'].replace(0, '', inplace=True)

    df_tp = pd.merge(df_tp, shangpin_jiage, on='product_bar_code', how='left')  # 此处建议还是让他们上传全的type表
    del shangpin_jiage, df_type

    ###赠品处理###

    zengpin_jiage = pd.read_sql_query('SELECT * FROM good_giveaway_item_price', engine)
    zengpin_jiage.drop(['id', 'upload_time', 'create_time'], axis=1, inplace=True)

    zengpin_jiage['giveaway'] = True

    df_tp = pd.merge(df_tp, zengpin_jiage, left_on='product_bar_code', right_on='giveaway_item_bar_code', how='left')
    del zengpin_jiage

    # 赠品 product_retail_price
    df_tp['good_giveaway_item_price'] = df_tp['product_num'] * df_tp['giveaway_item_price_per_pack']
    df_giveaway_item_price = df_tp.groupby('sixty_nine_code').agg(
        good_giveaway_item_price=('good_giveaway_item_price', 'sum')).reset_index()
    df_tp = df_tp.drop("good_giveaway_item_price", axis=1)
    df_tp = pd.merge(df_tp, df_giveaway_item_price, on='sixty_nine_code', how='left')
    del df_giveaway_item_price

    df_tp['giveaway'].fillna(False, inplace=True)
    # 选择giveaway取值为TRUE的行并保存到df_giveaway_item_combination中
    df_giveaway_item_combination = df_tp[df_tp['giveaway'] == True].copy()

    # 生成新变量giveaway_item_combination取值为变量pack_count(int)和type(str)的作为字符串粘贴后的值
    df_giveaway_item_combination['good_giveaway_item_combination'] = df_giveaway_item_combination['product_num'].astype(str) + '*' + \
                                                                df_giveaway_item_combination['product_code']
    df_giveaway_item_combination = df_giveaway_item_combination[['sixty_nine_code', 'good_giveaway_item_combination']]
    df_giveaway_item_combination = df_giveaway_item_combination.groupby('sixty_nine_code')['good_giveaway_item_combination'].agg(lambda x: '+'.join(x))
    df_tp = pd.merge(df_tp, df_giveaway_item_combination, on='sixty_nine_code', how='left')
    del df_giveaway_item_combination

    ### 正式生成系列等内容 ###

    # good_series
    # 找到每个分组中quantity最大值所对应的索引
    df_ttp = df_tp.loc[df_tp['product_series'] != '']
    max_quantity_idx = df_ttp.groupby('sixty_nine_code')['product_num'].transform('idxmax')
    # 使用索引获取相应的type值
    df_ttp.loc[:, 'good_series'] = df_ttp.loc[max_quantity_idx, 'product_series'].values
    df_ttp = df_ttp[['sixty_nine_code', 'good_series']].drop_duplicates()

    df_tp = df_tp.merge(df_ttp, how='left', on='sixty_nine_code')
    del max_quantity_idx, df_ttp

    # pack_count
    df_pack_count = df_tp.groupby('sixty_nine_code')['product_num'].sum().reset_index()
    df_pack_count.columns = ['sixty_nine_code', 'good_pack_count']
    df_tp = pd.merge(df_tp, df_pack_count, on='sixty_nine_code', how='left')
    del df_pack_count

    # 开单价 product_initial_price
    df_tp['good_initial_price'] = df_tp['product_num'] * df_tp['product_initial_price']
    df_initial_price = df_tp.groupby('sixty_nine_code').agg(
        good_initial_price=('good_initial_price', 'sum')).reset_index()
    df_tp = df_tp.drop("good_initial_price", axis=1)
    df_tp = pd.merge(df_tp, df_initial_price, on='sixty_nine_code', how='left')
    del df_initial_price

    # 零售价 product_retail_price
    df_tp['good_retail_price'] = df_tp['product_num'] * df_tp['product_retail_price']
    df_retail_price = df_tp.groupby('sixty_nine_code').agg(good_retail_price=('good_retail_price', 'sum')).reset_index()
    df_tp = df_tp.drop("good_retail_price", axis=1)
    df_tp = pd.merge(df_tp, df_retail_price, on='sixty_nine_code', how='left')
    del df_retail_price

    # 总片数 total_pieces
    df_tp['good_total_pieces'] = df_tp['product_num'] * df_tp['product_pack_pieces']
    df_total_pieces = df_tp.groupby('sixty_nine_code').agg(good_total_pieces=('good_total_pieces', 'sum')).reset_index()
    df_tp = df_tp.drop("good_total_pieces", axis=1)
    df_tp = pd.merge(df_tp, df_total_pieces, on='sixty_nine_code', how='left')
    del df_total_pieces

    # 大促 product_retail_price
    df_tp['good_big_promotion_price'] = df_tp['product_num'] * df_tp['product_big_promotion_price']
    df_big_promotion_price = df_tp.groupby('sixty_nine_code').agg(
        good_big_promotion_price=('good_big_promotion_price', 'sum')).reset_index()
    df_tp = df_tp.drop("good_big_promotion_price", axis=1)
    df_tp = pd.merge(df_tp, df_big_promotion_price, on='sixty_nine_code', how='left')
    del df_big_promotion_price

    # 日促 product_retail_price
    df_tp['good_small_promotion_price'] = df_tp['product_num'] * df_tp['product_small_promotion_price']
    df_small_promotion_price = df_tp.groupby('sixty_nine_code').agg(
        good_small_promotion_price=('good_small_promotion_price', 'sum')).reset_index()
    df_tp = df_tp.drop("good_small_promotion_price", axis=1)
    df_tp = pd.merge(df_tp, df_small_promotion_price, on='sixty_nine_code', how='left')
    del df_small_promotion_price

    df_69_info = df_tp.loc[:, ['sixty_nine_code', 'product_combination', 'good_series',
                               'good_initial_price', 'good_retail_price',
                               'good_total_pieces', 'good_pack_count',
                               'good_big_promotion_price', 'good_small_promotion_price',
                               'good_giveaway_item_price', 'good_giveaway_item_combination'
                               ]].drop_duplicates()
    del df_tp
    df_69_info.rename(columns={'product_combination': 'good_product_combination'}, inplace=True)
    df_69_info['good_giveaway_item_price_ratio'] = df_69_info['good_giveaway_item_price'] / df_69_info['good_initial_price']
    df_69_info['good_giveaway_item_combination'].replace(np.nan, '-', inplace=True)  # 将没有赠品组合的替换为-值
    df_69_info['create_time'] = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
    df_69_info.dropna(inplace=True)
    allupdate_insert(df_69_info,good_indicator)


'''—————————————————————————————方法：处理上传的产品明细和价格体系表入库，生成商品指标表—————————————————————————————'''
def good_product_related_upload_process(upload_df_product_details,upload_df_price_system_jietingsheet,upload_df_price_system_paiyangzhuangsheet):
    info='success'
    # try:
    upload_df_product_details=upload_df_product_details #产品明细上传表
    # 产品代码表《产品明细》单品sheet
    table_zh_name='产品代码表'
    good_product_code_df=upload_df_product_details['单品'][['款式编码','商家编码','商品名称']]
    good_product_code_df['商家编码'] = good_product_code_df['商家编码'].apply(lambda x: str(x).replace(' ', ''))
    upload_allupdate_insert(good_product_code_df, table_zh_name,good_product_code)

    # 产品组合表《产品明细》套餐sheet
    table_zh_name='产品组合表'
    good_product_combination_df=upload_df_product_details['套餐'][['套餐名称','套餐编码','商品名称','商家编码','数量']]
    good_product_combination_df['商家编码'] = good_product_combination_df['商家编码'].apply(lambda x: str(x).replace(' ', ''))
    upload_allupdate_insert(good_product_combination_df, table_zh_name,good_product_combination)

    # 产品价格表《价格体系》洁婷sheet 处理upload_df_price_system_jietingsheet
    table_zh_name='产品价格表'
    price_rename_mapping = {
        0: '开单价',
        1: '日销价',
        2: '小促价',
        3: '大促价'
    }
    price_columns = [col for col in upload_df_price_system_jietingsheet.columns if  '价格' in col]
    for i, col in enumerate(price_columns):
        upload_df_price_system_jietingsheet.rename(columns={col: price_rename_mapping[i]}, inplace=True)
    upload_df_price_system_jietingsheet['系列'] = upload_df_price_system_jietingsheet['系列'] .str.replace('\n', '', regex=False)
    upload_df_price_system_jietingsheet['系列'].fillna(method='ffill', inplace=True)
    upload_df_price_system_jietingsheet[upload_df_price_system_jietingsheet.filter(regex='价$').columns] = upload_df_price_system_jietingsheet[upload_df_price_system_jietingsheet.filter(regex='价$').columns].fillna(0)
    good_product_price_df=upload_df_price_system_jietingsheet[['系列', '代码', '条码', '名称', '包规', '零售价', '开单价', '日销价', '小促价', '大促价']]
    good_product_price_df['条码'] = good_product_price_df['条码'].apply(lambda x: str(x).replace(' ', ''))
    good_product_price_df = good_product_price_df.drop_duplicates(subset='条码', keep='last') #上传时就不应该重复，若重复保留越下面的记录
    upload_allupdate_insert(good_product_price_df, table_zh_name,good_product_price)

    # 赠品价格表《价格体系》派样装sheet upload_df_price_system_paiyangzhuangsheet
    table_zh_name='赠品价格表'
    good_giveaway_item_price_df=upload_df_price_system_paiyangzhuangsheet[['款式编码','商品条码','派送装费用（包/元）','派送装名称']]
    good_giveaway_item_price_df['商品条码'] = good_giveaway_item_price_df['商品条码'].apply(lambda x: str(x).replace(' ', ''))
    upload_allupdate_insert(good_giveaway_item_price_df, table_zh_name,good_giveaway_item_price)
    # except:
    #     info='上传失败：请检查数据表中字段是否符合模板要求'

    try:
        generate_69info()
    except Exception as e:
        return {'code': 400, 'msg': '上传后生成商品指标表失败'}
    # return info


'''—————————————————————————————方法：展示上传模块中对应数据库表内容—————————————————————————————'''
def display_uploaded(class_name,table_zh_name:str):
    #连接数据库
    session = get_session()
    query = session.query(class_name)
    df = pd.read_sql(query.statement, session.bind)
    del df['create_time']
    df=uploaded_field_corr_entozh(df,table_zh_name)
    df = df.fillna('nan')
    return df

def display_uploaded_ondate(class_name,table_zh_name:str,begin_date:str,end_date:str):
    #连接数据库
    session = get_session()
    query = session.query(class_name).filter(and_(class_name.stat_time >= begin_date,
                                                   class_name.stat_time <= end_date))
    df = pd.read_sql(query.statement, session.bind)
    del df['create_time']
    df=uploaded_field_corr_entozh(df,table_zh_name)
    df=df.fillna('nan')
    return df
